﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Cstudy.COM
{
    /// <summary>
    /// SQL工具
    /// </summary>
    public class SQLTools
    {
        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <returns></returns>
        private static SqlConnection createcon()
        {
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString());
            return sqlcon;
        }
        /// <summary>
        /// 执行分页查询
        /// </summary>
        /// <param name="storagename">存储过程名</param>
        /// <param name="parms">存储过程参数</param>
        /// <param name="currentPage">当前页数</param>
        /// <param name="pagesize">页面大小</param>
        /// <param name="num">返回数据总数</param>
        /// <param name="returnstring">返回参数名</param>
        /// <returns>查询表结果</returns>
        public static DataSet ExecuteQuery(string storagename, SqlParameter[] parms, int currentPage, int pagesize, out int num, string returnstring)
        {
            SqlConnection con = createcon();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storagename;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();

            try
            {
                con.Open();
                cmd.Connection = con;
                int startRow = (currentPage - 1) * pagesize;
                for (int i = 0; i < parms.GetLength(0); i++)
                {
                    cmd.Parameters.Add(parms[i]);
                }
                da.SelectCommand = cmd;
                da.Fill(ds, startRow, pagesize, "ds");
                num = int.Parse(cmd.Parameters[returnstring].Value.ToString());
                return ds;
            }
            catch
            {
                num = 0;
                return null;
            }
            finally
            {
                con.Dispose();
                con.Close();
                cmd.Dispose();
                da.Dispose();
            }

        }

        public static DataSet ExecuteQuery(string storagename, int currentPage, int pagesize, out int num, string returnstring)
        {
            SqlConnection con = createcon();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storagename;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();

            try
            {
                con.Open();
                cmd.Connection = con;
                int startRow = (currentPage - 1) * pagesize;
                da.SelectCommand = cmd;
                da.Fill(ds, startRow, pagesize, "ds");
                num = int.Parse(cmd.Parameters[returnstring].Value.ToString());
                return ds;
            }
            catch
            {
                num = 0;
                return null;
            }
            finally
            {
                con.Dispose();
                con.Close();
                cmd.Dispose();
                da.Dispose();
            }
        }

        public static DataSet ExecuteQuery(string storagename, SqlParameter[] parms, int currentPage, int pagesize)
        {
            SqlConnection con = createcon();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storagename;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();

            try
            {
                con.Open();
                cmd.Connection = con;
                int startRow = (currentPage - 1) * pagesize;
                for (int i = 0; i < parms.GetLength(0); i++)
                {
                    cmd.Parameters.Add(parms[i]);
                }
                da.SelectCommand = cmd;
                da.Fill(ds, startRow, pagesize, "ds");
                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {
                con.Dispose();
                con.Close();
                cmd.Dispose();
                da.Dispose();
            }
        }
    }
}
